const express = require('express');

const routes = express.Router();

//引入mysql
const mysql = require('mysql');
//创建连接池
var pool = mysql.createPool({
    connectionLimit : 10,
    host : 'localhost',
    user : 'root',
    password : 'root',
    database : 'personmessage'
})

routes.get('/',(req,resp)=>{
    //获取cookies
    var uid = req.cookies.uid;
    var port_id = req.cookies.port_id;
    var time = req.cookies.getTimes;
    var loginame = req.cookies.loginame;

    //console.log(time)
    var hous = new Date(eval(time)).getHours();
    //console.log(hous)
    var minutes = new Date(eval(time)).getMinutes();
    var seconds = new Date(eval(time)).getSeconds();
    var houss = (hous < 10) ? '0'+hous : hous;
    var minus = (minutes < 10) ? '0'+minutes : minutes;
    var secondss = (seconds < 10) ? '0'+seconds : seconds;
    var date = houss+':'+minus+':'+secondss;
    //console.log(date)
    //console.log(houss , minus , secondss)
    //    执行数据查看   `` : 解析字符串里面的变量
    var sql = `SELECT permsg.* , login.loginame FROM permsg LEFT JOIN login ON login.id = permsg.id WHERE permsg.uid = ${uid}`;
    //console.log(sql)
    pool.getConnection(function(err,connection){
        if(err) throw err;
        //    链接成功
        connection.query(sql,(error,result)=> {
            var sql_uid = `SELECT MIN(uid) as min , MAX(uid) as max FROM permsg`;
            //    链接成功
            connection.query(sql_uid,(e,res)=> {
                //    释放连接   关闭数据库
                connection.release();

                //判断职务
                if(port_id == 1){
                    var port = '普通员工';

                }else if(port_id == 2){
                    var port = '行政主管';

                }else if(port_id == 3){
                    var port = '财务总监';

                }

                //判断学历
                if(result[0].edu_id == 1){
                    var education = '高中';

                }else if(result[0].edu_id == 2){
                    var education = '大专';

                }else if(result[0].edu_id == 3){
                    var education = '本科';

                }else if(result[0].edu_id == 4){
                    var education = '硕士';

                }else if(result[0].edu_id == 5){
                    var education = '博士';
                }
                //获取当前时间戳  Date.now()
                var data = new Date().getTime();
                resp.render('add_msgpay',{
                    education : education,  //学历
                    port_id : port_id, //职务
                    result : result,
                    port : port,
                    date : date,  //当前时间
                    res : res,  //所有工号
                    loginame : loginame
                })

                if(e) throw e;
            })
            //sql语句出错执行报错处理
            if(error) throw error;
        })
    })
})
routes.post('/',(req,resp)=>{
    console.log(req.body);
    //工号
    var uid = req.body.uid.trim();
    //提交过来的时间
    var birtmp = req.body.birtmp;
    var birtmps = new Date(birtmp).getTime();
    // 月份
    var month = new Date(birtmps).getMonth()+1;
    // // 年份
    var year = new Date(birtmps).getFullYear();
    //kbi效绩
    var kbi = req.body.kbi;
    //提成
    var perfor = req.body.perfor;
    //学历
    var edu_id = req.body.edu_id;
//    员工身份
    var sta_id = req.body.sta_id;
    console.log(uid , birtmps , month , year , kbi , perfor , edu_id , sta_id )
//
    if(uid == '' || birtmp == ''){
        resp.redirect('add_msgpay')
    }else {
        var sql = `INSERT INTO pay (uid , month , year , kbi , perfor , edu_id , sta_id , status , port_id) VALUES ( ${uid} , ${month} , ${year} , ${kbi} , ${perfor} , ${edu_id} , ${sta_id} , 1 , ${sta_id})`;
        //console.log(sql)
        pool.getConnection(function(err,connection){
            if(err) throw err;
            //    链接成功
            connection.query(sql,(error,result)=> {
                //    释放连接   关闭数据库
                connection.release();

                if(result){
                    resp.redirect('sle_msgpay')
                }

                if(error) throw error;
            })
        })
    }


})
//暴露接口
module.exports = routes;


// SELECT stu_list.* , `subject`.sub_name FROM stu_list LEFT JOIN `subject` ON stu_list.sub_id = `subject`.id WHERE stu_list.uid = 1901